package com.study.util.excel;

import java.io.ByteArrayOutputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFFont;

/**
 * EXCEL导出
 * 
 * @author chenk
 *
 */
public class BigExcelExportUtil {
	/**
	 * 私有构造函数
	 */
	private BigExcelExportUtil() {

	}

	/**
	 * 构造EXCEL,并响应客户端
	 * 
	 * @param listValues
	 * @param fileName
	 * @param sheetName
	 * @param cellTitle
	 * @param cellValue
	 * @param res
	 */
	public static void buildXSLXExcel(List<Map<String, Object>> listValues, String fileName, String sheetName,
			String[] cellTitle, String[] cellValue, HttpServletResponse res) {
		byte[] bytes = buildXSLXExcelWithSheetName(listValues, sheetName, cellTitle, cellValue);
		if (0 == bytes.length) {
			return;
		}
		writeDocResponse(res, bytes, fileName);
	}

	/**
	 * 构建excel
	 * 
	 * @param listValues
	 * @param sheetName
	 * @param cellTitle
	 * @param cellValue
	 * @return
	 */
	private static byte[] buildXSLXExcelWithSheetName(List<Map<String, Object>> listValues, String sheetName,
			String[] cellTitle, String[] cellValue) {
		try (ByteArrayOutputStream outStream = new ByteArrayOutputStream();
				SXSSFWorkbook workbook = createExcelBook(listValues, sheetName, cellTitle, cellValue);) {
			workbook.write(outStream);
			outStream.flush();
			workbook.dispose();
			return outStream.toByteArray();
		} catch (Exception e) {
			e.printStackTrace();
			return new byte[0];
		}
	}

	/**
	 * 生成WorkBook
	 * 
	 * @param listValues
	 * @param sheetName
	 * @param cellTitle
	 * @param cellValue
	 * @return
	 */
	private static SXSSFWorkbook createExcelBook(List<Map<String, Object>> listValues, String sheetName,
			String[] cellTitle, String[] cellValue) {
		// 创建工作簿
		SXSSFWorkbook workBook = new SXSSFWorkbook();
		Sheet sheet = workBook.createSheet();

		// 工作簿名称
		workBook.setSheetName(0, sheetName);

		// 字体设置
		Font font = workBook.createFont();
		font.setColor(XSSFFont.COLOR_NORMAL);
		font.setBold(true);

		// 创建格式
		CellStyle cellStyle = workBook.createCellStyle();
		cellStyle.setFont(font);
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

		// 创建第一行标题
		Row titleRow = sheet.createRow((short) 0);
		for (int i = 0; i < cellTitle.length; i++) {
			// 创建第一行标题单元格
			sheet.setColumnWidth(i, 20 * 256);
			Cell cell = titleRow.createCell(i, CellType.STRING);
			cell.setCellStyle(cellStyle);
			cell.setCellType(CellType.STRING);
			cell.setCellValue(cellTitle[i]);
		}

		// 第二行开始写入数据
		if (listValues != null && listValues.size() > 0) {
			// 创建格式
			CellStyle style = workBook.createCellStyle();
			// 遍历列表数据
			for (int i = 0; i < listValues.size(); i++) {
				Row row = sheet.createRow(i + 1);
				setRow(cellTitle, cellValue, workBook, sheet, style, listValues.get(i), i, row);
			}
		}

		return workBook;
	}

	private static void setRow(String[] cellTitle, String[] cellValue, SXSSFWorkbook workBook, Sheet sheet,
			CellStyle style, Map<String, Object> map, int line, Row row) {
		for (int j = 0; j < cellTitle.length; j++) {
			// 在上面行索引0的位置创建单元格
			// XSSFCell cell = titleRow.createCell(i, 0);
			Cell cell = row.createCell(j, CellType.STRING);
			// 定义单元格为字符串类型
			// cell.setCellType(XSSFCell.CELL_TYPE_STRING);
			cell.setCellType(CellType.STRING);
			// 取出列表值
			cell.setCellValue(getMapString(map, cellValue[j]));
			// style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
			style.setAlignment(HorizontalAlignment.CENTER);
			cell.setCellStyle(style);
		}
	}

	/**
	 * 输出文件到客户端 <功能详细描述>
	 *
	 * @param rsp      http响应对象
	 * @param bytes
	 * @param fileName
	 * @see [类、类#方法、类#成员]
	 */
	private static void writeDocResponse(HttpServletResponse rsp, byte[] bytes, String fileName) {
		try (OutputStream out = rsp.getOutputStream();) {
			// 扩展名获取ContentType
			rsp.setContentType("application/vnd.ms-excel");
			String fileNameURL = URLEncoder.encode(fileName, "UTF-8");
			String contentHeader = "attachment; filename=" + fileNameURL + ";" + "filename*=utf-8''" + fileNameURL;
			rsp.setHeader("Content-disposition", contentHeader);
			// 文件写入
			out.write(bytes, 0, bytes.length);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private static String getMapString(Map<String, Object> map, String key) {
		return map.get(key) == null ? null : String.valueOf(map.get(key));
	}
}
